package com.es.dao.impl;

import com.es.bean.Student;
import com.es.dao.StudentDaoI;
import com.es.util.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;

/**
 * 描述:
 * es-jsp-
 *
 * @author Derek
 * 创建时间：2022-04-25 10:10
 */
public class StudentDaoImpl implements StudentDaoI {
    @Override
    public boolean save(Student student) {
        //
        String sql = "insert into student(name,sex,age,address,telephone) values(?,?,?,?,?)";
        Connection connection = DBUtils.getConnection();
        try {
            connection.setAutoCommit(false);
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getSex());
            preparedStatement.setInt(3, student.getAge());
            preparedStatement.setString(4, student.getAddress());
            preparedStatement.setString(5, student.getTelephone());
            int i = preparedStatement.executeUpdate();
            connection.commit();
            if(i > 0){
                return true;
            }
            connection.close();//
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean update(Student student) {
        String sql = "update student set name = ? , age = ?, sex = ?, address = ?, telephone=? where id = ?";
        Connection connection = DBUtils.getConnection();
        try {
            connection.setAutoCommit(false);
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setInt(2, student.getAge());
            ps.setString(3, student.getSex());
            ps.setString(4, student.getAddress());
            ps.setString(5, student.getTelephone());
            ps.setInt(6, student.getId());
            int i = ps.executeUpdate();
            connection.commit();
            ps.close();
            connection.close();
            if(i>0){
                return true;
            }
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public List<Student> list() {
        List<Student> list = new ArrayList<>();
        String sql = "select id,name,age,sex,address,telephone from student";
        Connection connection = DBUtils.getConnection();
        try {
            PreparedStatement ps  = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Student student = new Student();
                if("M".equals(rs.getString("sex"))){
                    student.setSex("男");
                }else{
                    student.setSex("女");
                }
                student.setTelephone(rs.getString("telephone"));
                student.setAge(rs.getInt("age"));
                student.setName(rs.getString("name"));
                student.setAddress(rs.getString("address"));
                student.setId(rs.getInt("id"));
                list.add(student);
            }

            rs.close();
            ps.close();
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Student get(Integer id) {
        //
        String sql = "select name,age,sex,telephone,address from student where id = ?";
        Connection connection = DBUtils.getConnection();
        //
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
                Student student = new Student();
                student.setId(id);
                student.setName(rs.getString(1));
                student.setAge(rs.getInt(2));
                student.setSex(rs.getString(3));
                student.setTelephone(rs.getString(4));
                student.setAddress(rs.getString(5));
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean delete(Integer id) {
        String sql = "delete from student where id = ?";
        Connection connection = DBUtils.getConnection();
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            int i = ps.executeUpdate();
            if(i>0){
                return true;
            }
            ps.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
